package com.analysis.service;

import com.analysis.dto.PageResponse;
import com.analysis.dto.ReferencesImportRequest;
import com.analysis.dto.ReferencesPageRequest;
import com.analysis.entity.ProductDomain;
import com.analysis.entity.ProjectBrand;
import com.analysis.entity.References;
import com.analysis.enums.ModelChatClientEnum;
import com.analysis.enums.WebTypeEnum;
import com.analysis.mapper.ProductDomainMapper;
import com.analysis.mapper.ReferencesMapper;
import com.analysis.utils.ExcelExportUtils;
import com.analysis.utils.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 信源服务类
 */
@Service
@Transactional
public class ReferencesService {

    @Autowired
    private ReferencesMapper referencesMapper;

    @Autowired
    private ProductDomainMapper productDomainMapper;
    @Autowired
    private ProjectBrandService projectBrandService;

    /**
     * 创建信源
     */
    public int createReferences(References references) {
        if (references.getReferenceCount() == null) {
            references.setReferenceCount(1);
        }
        // 校验URL格式，过滤无效URL
        if (!StringUtil.isValidUrl(references.getUrl())) {
            throw new IllegalStateException("url格式不正确");
        }

        // 域名
        references.setWebName(StringUtil.extractDomainFromUrl(references.getUrl()));

        // 品牌名称
        if (StringUtils.isBlank(references.getProductName())) {
            ProjectBrand projectBrand = projectBrandService.selectByProjectId(references.getProjectId());
            references.setProductName(projectBrand != null ? projectBrand.getProductName() : null);
        }
        // AI模型
        references.setAiModel(ModelChatClientEnum.getSource(references.getAiModel()));

        // 网站类型
        Map<String, String> productDomainMap = getProductDomainMap();
        String domain = productDomainMap.get(references.getProductName());
        if (domain != null) {
            references.setWebType(references.getUrl().contains(domain) ? WebTypeEnum.WEB_SITE.getSource() : WebTypeEnum.THIRD_SITE.getSource());
        }
        return referencesMapper.insert(references);
    }

    /**
     * 批量查询信源（根据URL列表）
     * @param urls URL列表
     * @return URL到References的映射
     */
    public Map<String, References> getReferencesByUrls(List<String> urls) {
        if (urls == null || urls.isEmpty()) {
            return new HashMap<>();
        }
        List<References> referencesList = referencesMapper.selectByUrls(urls);
        return referencesList.stream()
                .collect(Collectors.toMap(References::getUrl, ref -> ref, (existing, replacement) -> existing));
    }

    /**
     * 批量更新信源
     * @param referencesList 信源列表
     * @return 更新成功的数量
     */
    public int batchUpdateReferences(List<References> referencesList) {
        if (referencesList == null || referencesList.isEmpty()) {
            return 0;
        }
        return referencesMapper.batchUpdateReferences(referencesList);
    }

    /**
     * 根据ID删除信源（逻辑删除）
     */
    public int deleteReferences(Long id) {
        return referencesMapper.logicalDeleteById(id);
    }

    /**
     * 更新信源信息
     */
    public int updateReferences(References references) {
        return referencesMapper.update(references);
    }

    /**
     * 根据ID查询信源
     */
    public References getReferencesById(Long id) {
        return referencesMapper.selectById(id);
    }

    /**
     * 根据URL查询信源
     */
    public References getReferencesByUrl(String url) {
        return referencesMapper.selectByUrl(url);
    }

    /**
     * 根据项目ID查询信源列表
     */
    public List<References> getReferencesByProjectId(Long projectId) {
        return referencesMapper.selectByProjectId(projectId);
    }

    /**
     * 根据品牌名称查询信源列表
     */
    public List<References> getReferencesByProductName(String productName) {
        return referencesMapper.selectByProductName(productName);
    }

    /**
     * 根据AI模型查询信源列表
     */
    public List<References> getReferencesByAiModel(String aiModel) {
        return referencesMapper.selectByAiModel(aiModel);
    }

    /**
     * 根据网站类型查询信源列表
     */
    public List<References> getReferencesByWebType(String webType) {
        return referencesMapper.selectByWebType(webType);
    }

    /**
     * 查询所有信源
     */
    public List<References> getAllReferences() {
        return referencesMapper.selectAll();
    }

    /**
     * 分页查询信源
     */
    public List<References> getReferencesByPage(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;
        return referencesMapper.selectByPage(offset, pageSize);
    }

    /**
     * 统计信源总数
     */
    public int countReferences() {
        return referencesMapper.countAll();
    }

    /**
     * 根据条件查询信源
     */
    public List<References> getReferencesByCondition(Long projectId, String productName, String aiModel, String webType, Integer enableStatus) {
        return referencesMapper.selectByCondition(projectId, productName, aiModel, webType, enableStatus);
    }

    /**
     * 根据品牌名称和问题查询信源
     */
    public List<References> getReferencesByProductAndQuestion(String productName, String question) {
        return referencesMapper.selectByProductAndQuestion(productName, question);
    }

    /**
     * 根据品牌名称和URL查询信源
     */
    public List<References> getReferencesByProductAndUrl(String productName, String url) {
        return referencesMapper.selectByProductAndUrl(productName, url);
    }

    /**
     * 批量创建信源
     */
    public int batchCreateReferences(List<References> referencesList) {
        int count = 0;
        for (References references : referencesList) {
            if (references.getEnableStatus() == null) {
                references.setEnableStatus(1);
            }
            if (references.getReferenceCount() == null) {
                references.setReferenceCount(1);
            }
            count += referencesMapper.insert(references);
        }
        return count;
    }

    /**
     * 增加引用次数
     */
    public int incrementReferenceCount(Long id) {
        References references = referencesMapper.selectById(id);
        if (references != null) {
            references.setReferenceCount(references.getReferenceCount() + 1);
            return referencesMapper.update(references);
        }
        return 0;
    }

    /**
     * 根据项目ID统计信源数量
     */
    public int countReferencesByProjectId(Long projectId) {
        return (int) referencesMapper.countByPageWithConditions(null, null, null, projectId, null, null, null, null, null);
    }

    /**
     * 根据品牌名称统计信源数量
     */
    public int countReferencesByProductName(String productName) {
        return (int) referencesMapper.countByPageWithConditions(productName, null, null, null, null, null, null, null, null);
    }

    /**
     * 根据AI模型统计信源数量
     */
    public int countReferencesByAiModel(String aiModel) {
        return (int) referencesMapper.countByPageWithConditions(null, null, aiModel, null, null, null, null, null, null);
    }

    /**
     * 多条件分页查询信源
     */
    public PageResponse<References> getReferencesByPageWithConditions(ReferencesPageRequest request) {
        // 计算偏移量
        int offset = (request.getPageNum() - 1) * request.getPageSize();

        // 查询数据
        List<References> data = referencesMapper.selectByPageWithConditions(
                request.getProductName(),
                request.getQuestion(),
                request.getAiModel(),
                request.getProjectId(),
                request.getWebName(),
                request.getPageName(),
                request.getUrl(),
                request.getAddType(),
                request.getWebType(),
                offset,
                request.getPageSize()
        );

        // 查询总数
        long total = referencesMapper.countByPageWithConditions(
                request.getProductName(),
                request.getQuestion(),
                request.getAiModel(),
                request.getProjectId(),
                request.getWebName(),
                request.getPageName(),
                request.getUrl(),
                request.getAddType(),
                request.getWebType()
        );

        return new PageResponse<>(data, total, request.getPageNum(), request.getPageSize());
    }

    /**
     * 导出References数据到Excel
     *
     * @param request 查询请求参数
     * @return Excel文件的字节数组
     * @throws IOException IO异常
     */
    public byte[] exportReferencesToExcel(ReferencesPageRequest request) throws IOException {
        // 查询数据
        List<References> referencesList = referencesMapper.selectByPageWithConditions(
                request.getProductName(),
                request.getQuestion(),
                request.getAiModel(),
                request.getProjectId(),
                request.getWebName(),
                request.getPageName(),
                request.getUrl(),
                request.getAddType(),
                request.getWebType(),
                0,
                Integer.MAX_VALUE
        );

        // 生成工作表名称
        String sheetName = generateSheetName(request);

        // 导出到Excel
        return ExcelExportUtils.exportReferencesToExcel(referencesList, sheetName);
    }

    /**
     * 生成工作表名称
     *
     * @param request 查询请求参数
     * @return 工作表名称
     */
    private String generateSheetName(ReferencesPageRequest request) {
        StringBuilder sheetName = new StringBuilder("信源数据");

        if (request.getProductName() != null && !request.getProductName().trim().isEmpty()) {
            sheetName.append("_").append(request.getProductName());
        }

        if (request.getAiModel() != null && !request.getAiModel().trim().isEmpty()) {
            sheetName.append("_").append(request.getAiModel());
        }

        if (request.getWebName() != null && !request.getWebName().trim().isEmpty()) {
            sheetName.append("_").append(request.getWebName());
        }

        return sheetName.toString();
    }

    /**
     * 批量更新web_type：根据product_name匹配product表，根据URL是否包含domain设置web_type
     *
     * @param ids         ID列表（可选）
     * @param productName 产品名称（可选，支持模糊查询）
     * @param projectId   项目ID（可选）
     * @param aiModel     AI模型（可选）
     * @return 更新统计信息
     */
    @Transactional
    public Map<String, Object> batchUpdateWebTypeByProductMatch(List<Long> ids, String productName, Long projectId, String aiModel) {
        Map<String, Object> result = new HashMap<>();
        try {
            //  根据条件查询web_type为null的references（直接使用SQL筛选）
            List<References> filteredReferences = referencesMapper.selectByNullWebTypeWithConditions(ids, productName, projectId, aiModel);
            result.put("filteredNullWebType", filteredReferences.size());

            if (filteredReferences.isEmpty()) {
                result.put("message", "没有找到符合条件且web_type为null的数据");
                result.put("updatedCount", 0);
                result.put("matchedCount", 0);
                result.put("unmatchedCount", 0);
                return result;
            }

            // 3. 查询所有product的product_name和domain对应关系
            Map<String, String> productDomainMap = getProductDomainMap();

            // 4. 分类收集需要更新的ID
            List<Long> matchedIds = new ArrayList<>(); // URL包含domain的ID（设置为1）
            List<Long> unmatchedIds = new ArrayList<>(); // URL不包含domain（设置为2）

            for (References reference : filteredReferences) {
                if (reference.getProductName() == null || reference.getUrl() == null) {
                    continue;
                }
                // 查找匹配的product
                String domain = productDomainMap.get(reference.getProductName());
                if (domain != null) {
                    // 判断URL是否包含domain
                    if (reference.getUrl().contains(domain)) {
                        matchedIds.add(reference.getId());
                    } else {
                        unmatchedIds.add(reference.getId());
                    }
                }
            }

            // 5. 批量更新
            int matchedCount = 0;
            int unmatchedCount = 0;
            if (!matchedIds.isEmpty()) {
                matchedCount = referencesMapper.batchUpdateWebTypeByIds(matchedIds, 1);
            }
            if (!unmatchedIds.isEmpty()) {
                unmatchedCount = referencesMapper.batchUpdateWebTypeByIds(unmatchedIds, 2);
            }
            int updatedCount = matchedCount + unmatchedCount;
            result.put("updatedCount", updatedCount);
            result.put("matchedCount", matchedCount);
            result.put("unmatchedCount", unmatchedCount);
        } catch (Exception e) {
            result.put("error", "批量更新过程中发生错误: " + e.getMessage());
            result.put("updatedCount", 0);
        }

        return result;
    }

    /**
     * 统计webType数量
     * 先执行批量更新，再统计数量
     *
     * @param ids         ID列表（可选）
     * @param productName 产品名称（可选，支持模糊查询）
     * @param projectId   项目ID（可选）
     * @param aiModel     AI模型（可选）
     * @return 包含webType=1和webType=2的总数
     */
    public Map<String, Object> getWebTypeStatistics(List<Long> ids, String productName, Long projectId, String aiModel) {
        Map<String, Object> result = new HashMap<>();
        try {
            // 1. 先执行批量更新web_type
            Map<String, Object> updateResult = batchUpdateWebTypeByProductMatch(ids, productName, projectId, aiModel);
            result.put("batchUpdateResult", updateResult);

            // 2. 统计webType=1的数量（官网）
            int webType1Count = referencesMapper.countByWebTypeWithConditions(1, ids, productName, projectId, aiModel);

            // 3. 统计webType=2的数量（其他）
            int webType2Count = referencesMapper.countByWebTypeWithConditions(2, ids, productName, projectId, aiModel);

            result.put("websiteCount", webType1Count);
            result.put("otherCount", webType2Count);
            result.put("totalCount", webType1Count + webType2Count);
        } catch (Exception e) {
            result.put("message", "批量更新和统计过程中发生错误: " + e.getMessage());
            result.put("websiteCount", 0);
            result.put("otherCount", 0);
            result.put("totalCount", 0);
        }
        return result;
    }


    public Map<String, String> getProductDomainMap() {
        List<ProductDomain> allProductDomains = productDomainMapper.listAll();
        Map<String, String> productDomainMap = new HashMap<>();
        for (ProductDomain productDomain : allProductDomains) {
            if (productDomain.getProductName() != null && productDomain.getDomain() != null) {
                productDomainMap.put(productDomain.getProductName(), productDomain.getDomain());
            }
        }
        return productDomainMap;
    }

    /**
     * 导入Excel数据到References表
     *
     * @param request   导入请求
     * @param projectId 项目ID
     * @return 导入结果
     */
    @Transactional
    public Map<String, Object> importReferencesFromExcel(ReferencesImportRequest request, Long projectId) {
        Map<String, Object> result = new HashMap<>();

        try {
            if (request.getItems() == null || request.getItems().isEmpty()) {
                result.put("message", "导入数据为空");
                result.put("successCount", 0);
                result.put("duplicateCount", 0);
                result.put("errorCount", 0);
                return result;
            }
            Map<String, String> productDomainMap = getProductDomainMap();
            ProjectBrand projectBrand = projectBrandService.selectByProjectId(projectId);
            List<ReferencesImportRequest.ReferencesImportItem> items = request.getItems();
            List<References> toInsert = new ArrayList<>();
            List<String> duplicates = new ArrayList<>();
            int errorCount = 0;

            // 1. 数据验证和去重检查
            for (ReferencesImportRequest.ReferencesImportItem item : items) {
                try {
                    // 校验URL格式，过滤无效URL
                    if (!StringUtil.isValidUrl(item.getUrl())) {
                        continue;
                    }
                    // 品牌名称
                    if (StringUtils.isBlank(item.getProductName())) {
                        item.setProductName(projectBrand != null ? projectBrand.getProductName() : null);
                    }
                    // 检查重复数据
                    int duplicateCount = referencesMapper.countByUrlAndProductName(item.getUrl(), item.getProductName());
                    if (duplicateCount > 0) {
                        duplicates.add(item.getUrl() + "|" + item.getProductName());
                        continue;
                    }

                    // 创建References对象
                    References references = new References();
                    references.setProjectId(projectId);
                    references.setPageName(item.getPageName());
                    references.setUrl(item.getUrl());
                    references.setWebName(StringUtil.extractDomainFromUrl(references.getUrl()));
                    references.setProductName(item.getProductName());
                    references.setAiModel(ModelChatClientEnum.getSource(item.getAiModel()));
                    references.setQuestion(item.getQuestion());
                    references.setEnableStatus(1);
                    references.setReferenceCount(1);
                    references.setAddType(1); // 手动添加
                    // 自动设置webType
                    if (references.getProductName() != null && references.getUrl() != null) {
                        String domain = productDomainMap.get(references.getProductName());
                        if (domain != null) {
                            references.setWebType(references.getUrl().contains(domain) ?
                                    WebTypeEnum.WEB_SITE.getSource() : WebTypeEnum.THIRD_SITE.getSource());
                        }
                    }
                    toInsert.add(references);
                } catch (Exception e) {
                    errorCount++;
                    // 记录错误日志
                    System.err.println("处理导入数据时发生错误: " + e.getMessage() + ", 数据: " + item);
                }
            }

            // 2. 批量插入数据
            int successCount = 0;
            if (!toInsert.isEmpty()) {
                for (References references : toInsert) {
                    try {
                        int insertResult = referencesMapper.insert(references);
                        if (insertResult > 0) {
                            successCount++;
                        }
                    } catch (Exception e) {
                        errorCount++;
                        System.err.println("插入数据时发生错误: " + e.getMessage() + ", 数据: " + references);
                    }
                }
            }

            // 3. 返回结果
            result.put("totalCount", items.size());
            result.put("successCount", successCount);
            result.put("duplicateCount", duplicates.size());
            result.put("errorCount", errorCount);
            result.put("duplicates", duplicates);

        } catch (Exception e) {
            result.put("error", "导入过程中发生错误: " + e.getMessage());
            result.put("successCount", 0);
            result.put("duplicateCount", 0);
            result.put("errorCount", 0);
        }

        return result;
    }

    /**
     * 从Excel文件导入References数据
     *
     * @param file      Excel文件
     * @param projectId 项目ID
     * @return 导入结果
     */
    @Transactional
    public Map<String, Object> importReferencesFromExcelFile(MultipartFile file, Long projectId) {
        Map<String, Object> result = new HashMap<>();

        try {
            // 解析Excel文件
            List<ReferencesImportRequest.ReferencesImportItem> items = parseExcelFile(file);
            if (items.isEmpty()) {
                result.put("message", "Excel文件中没有有效数据");
                result.put("successCount", 0);
                result.put("duplicateCount", 0);
                result.put("errorCount", 0);
                return result;
            }

            // 创建导入请求
            ReferencesImportRequest request = new ReferencesImportRequest();
            request.setItems(items);

            // 执行导入
            return importReferencesFromExcel(request, projectId);

        } catch (Exception e) {
            result.put("error", "解析Excel文件时发生错误: " + e.getMessage());
            result.put("successCount", 0);
            result.put("duplicateCount", 0);
            result.put("errorCount", 0);
            return result;
        }
    }

    /**
     * 解析Excel文件
     *
     * @param file Excel文件
     * @return 解析后的数据列表
     */
    private List<ReferencesImportRequest.ReferencesImportItem> parseExcelFile(MultipartFile file) throws IOException {
        List<ReferencesImportRequest.ReferencesImportItem> items = new ArrayList<>();

        Workbook workbook = null;
        try {
            // 根据文件扩展名选择工作簿类型
            String filename = file.getOriginalFilename();
            if (filename != null && filename.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(file.getInputStream());
            } else if (filename != null && filename.endsWith(".xls")) {
                workbook = new HSSFWorkbook(file.getInputStream());
            } else {
                throw new IllegalArgumentException("不支持的文件格式，请上传.xlsx或.xls文件");
            }

            Sheet sheet = workbook.getSheetAt(0); // 读取第一个工作表

            // 获取表头行
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new IllegalArgumentException("Excel文件格式错误，缺少表头行");
            }

            // 找到各列的索引
            Map<String, Integer> columnIndexMap = new HashMap<>();
            for (Cell cell : headerRow) {
                if (cell.getCellType() == CellType.STRING) {
                    String columnName = cell.getStringCellValue().trim();
                    columnIndexMap.put(columnName, cell.getColumnIndex());
                }
            }

            // 检查必需的列是否存在
            if (!columnIndexMap.containsKey("URL")) {
                throw new IllegalArgumentException("Excel文件必须包含'URL'列");
            }

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                ReferencesImportRequest.ReferencesImportItem item = new ReferencesImportRequest.ReferencesImportItem();

                // 读取各列数据
                item.setPageName(getCellStringValue(row, columnIndexMap.get("页面名称")));
                item.setUrl(getCellStringValue(row, columnIndexMap.get("URL")));
                item.setProductName(getCellStringValue(row, columnIndexMap.get("产品名称")));
                item.setAiModel(getCellStringValue(row, columnIndexMap.get("AI模型")));
                item.setQuestion(getCellStringValue(row, columnIndexMap.get("问题")));

                // 只添加有URL和产品名称的数据
                if (item.getUrl() != null && !item.getUrl().trim().isEmpty()) {
                    items.add(item);
                }
            }

        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }

        return items;
    }

    /**
     * 获取单元格的字符串值
     *
     * @param row         行
     * @param columnIndex 列索引
     * @return 字符串值
     */
    private String getCellStringValue(Row row, Integer columnIndex) {
        if (columnIndex == null) return null;

        Cell cell = row.getCell(columnIndex);
        if (cell == null) return null;

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
}

